﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace PominaScale.DataAccess
{
    public class UserDAO : IUserDAO
    {

        #region Properties
        private UserModel _searchObject;
        public UserModel SearchObject
        {
            get { return _searchObject; }
            set
            {
                if (_searchObject != value)
                {
                    _searchObject = value;
                }
            }
        }
        #endregion

        #region Methods
        /// <summary>
        /// Serach Method
        /// </summary>
        /// <returns></returns>
        public IList<UserModel> Search()
        {
            IList<UserModel> collection;
            using (VssTqhDataContext dataContext = new VssTqhDataContext(define.CONNECTIONSTRING))
            {
                IQueryable<User> query = dataContext.Users;
                if (!String.IsNullOrEmpty(_searchObject.Username))
                {
                    query = query.Where(e => e.Username.Contains(_searchObject.Username));
                }
                if (!String.IsNullOrEmpty(_searchObject.Fullname))
                {
                    query = query.Where(e => e.Fullname.Contains(_searchObject.Fullname));
                }

                collection = new List<UserModel>();
                foreach (var item in query)
                {
                    collection.Add(UserMapper.ToModel(item));
                }
            }

            return collection;
        }

        /// <summary>
        /// Get Last Method
        /// </summary>
        /// <returns></returns>
        public UserModel GetLast()
        {
            UserModel model = null;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // Get last entity from database
                    var entity = biocode.Users.LastOrDefault();

                    // If entity exists
                    if (entity != null)
                    {
                        // Set value to model
                        model = UserMapper.ToModel(entity);
                    }
                }
            }
            catch (Exception ex)
            {
                // Get last failed
                Debug.WriteLine(ex.Message);
            }

            return model;
        }

        /// <summary>
        /// Get All Method
        /// </summary>
        /// <returns></returns>
        public IList<UserModel> GetAll()
        {
            IList<UserModel> collection = null;//new List<UserModel>(); 
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // Get collection
                    var query = biocode.Users.ToList();

                    // If exists least one record.
                    if (query != null && query.Count > 0)
                    {
                        UserModel model;
                        collection = new List<UserModel>();
                        foreach (var entity in query)
                        {
                            model = UserMapper.ToModel(entity);
                            model.IsNew = false;
                            model.IsEdit = false;
                            collection.Add(model);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // Get all failed
                Debug.WriteLine(ex.Message);
            }

            return collection;
        }

        /// <summary>
        /// Get All Active Method
        /// </summary>
        /// <returns></returns>
        public IList<UserModel> GetAllActive()
        {
            IList<UserModel> collection = null;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // Get collection
                    var query = biocode.Users.ToList();

                    // If exists least one record.
                    if (query != null && query.Count > 0)
                    {
                        UserModel model;
                        collection = new List<UserModel>();
                        foreach (var entity in query)
                        {
                            model = UserMapper.ToModel(entity);
                            collection.Add(model);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // Get all failed
                Debug.WriteLine(ex.Message);
            }

            return collection;
        }

        /// <summary>
        /// Add Method
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool Add(UserModel item)
        {
            bool result = false;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // New entity and set value to it
                    User entity = new User();
                    UserMapper.ToEntity(entity, item);

                    // Execute Insertion
                    biocode.Users.InsertOnSubmit(entity);
                    biocode.SubmitChanges();

                    // Insert successful
                    result = true;
                    item.IsNew = false;
                    item.IsEdit = false;
                }
            }
            catch (Exception ex)
            {
                // Insert failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        /// <summary>
        /// Add Collection Method
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool Add(IList<UserModel> collection,
            out int success, out int failed)
        {
            success = 0;
            failed = 0;
            bool result = false;

            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    foreach (var item in collection)
                    {
                        // New entity and set value to it
                        User entity = new User();
                        UserMapper.ToEntity(entity, item);

                        // Execute Insertion
                        biocode.Users.InsertOnSubmit(entity);

                        item.IsNew = false;
                    }

                    try
                    {
                        biocode.SubmitChanges(ConflictMode.ContinueOnConflict);
                    }
                    catch (ChangeConflictException e)
                    {
                        Console.WriteLine("Optimistic concurrency error.");
                        Console.WriteLine(e.Message);
                        Console.ReadLine();
                        foreach (ObjectChangeConflict occ in biocode.ChangeConflicts)
                        {
                            MetaTable metatable = biocode.Mapping.GetTable(occ.Object.GetType());
                            User entityInConflict = (User)occ.Object;
                            Console.WriteLine("Table name: {0}", metatable.TableName);
                            Console.Write("User ID: ");
                            Console.WriteLine(entityInConflict.Username);
                            var model = collection.Where(m => m.Username == entityInConflict.Username).SingleOrDefault();
                            if (model != null)
                            {
                                model.IsNew = true;
                                foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                                {
                                    object currVal = mcc.CurrentValue;
                                    object origVal = mcc.OriginalValue;
                                    object databaseVal = mcc.DatabaseValue;
                                    MemberInfo mi = mcc.Member;
                                    Console.WriteLine("Member: {0}", mi.Name);
                                    Console.WriteLine("current value: {0}", currVal);
                                    Console.WriteLine("original value: {0}", origVal);
                                    Console.WriteLine("database value: {0}", databaseVal);
                                }
                            }

                            failed++;
                        }
                    }
                    success = collection.Count - failed;
                    result = (failed == 0);
                }
            }
            catch (Exception ex)
            {
                // Insert failed
                Debug.WriteLine(ex.Message);
            }
            return result;
        }

        /// <summary>
        /// Edit Method
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool Edit(UserModel item)
        {
            bool result = false;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // Get single entity from database
                    var entity = biocode.Users.Where(a => a.Username == item.Username).SingleOrDefault();

                    if (entity != null)
                    {

                        // Update entity value
                        UserMapper.ToEntity(entity, item);

                        // Execute Updated
                        biocode.SubmitChanges();

                        // Update successful
                        result = true;
                        item.IsEdit = false;
                    }
                }
            }
            catch (Exception ex)
            {
                // Update failed
                Debug.WriteLine(ex.Message);
                throw ex;
            }

            return result;
        }

        /// <summary>
        /// Edit Collection Method
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool Edit(IList<UserModel> collection,
            out int success, out int failed)
        {
            success = 0;
            failed = 0;
            bool result = false;

            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    User entity;

                    foreach (var item in collection)
                    {
                        // Get single entity from database
                        entity = biocode.Users.Where(a => a.Username == item.Username).SingleOrDefault();

                        if (entity != null)
                        {
                            // Update entity value
                            UserMapper.ToEntity(entity, item);

                            item.IsEdit = false;
                        }
                    }

                    try
                    {
                        biocode.SubmitChanges(ConflictMode.ContinueOnConflict);
                    }
                    catch (ChangeConflictException e)
                    {
                        Console.WriteLine("Optimistic concurrency error.");
                        Console.WriteLine(e.Message);
                        Console.ReadLine();
                        foreach (ObjectChangeConflict occ in biocode.ChangeConflicts)
                        {
                            MetaTable metatable = biocode.Mapping.GetTable(occ.Object.GetType());
                            User entityInConflict = (User)occ.Object;
                            Console.WriteLine("Table name: {0}", metatable.TableName);
                            Console.Write("User ID: ");
                            Console.WriteLine(entityInConflict.Username);
                            var model = collection.Where(m => m.Username == entityInConflict.Username).SingleOrDefault();
                            if (model != null)
                            {
                                model.IsEdit = true;
                                foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                                {
                                    object currVal = mcc.CurrentValue;
                                    object origVal = mcc.OriginalValue;
                                    object databaseVal = mcc.DatabaseValue;
                                    MemberInfo mi = mcc.Member;
                                    Console.WriteLine("Member: {0}", mi.Name);
                                    Console.WriteLine("current value: {0}", currVal);
                                    Console.WriteLine("original value: {0}", origVal);
                                    Console.WriteLine("database value: {0}", databaseVal);
                                }
                            }

                            failed++;
                        }
                    }
                    success = collection.Count - failed;
                    result = (failed == 0);
                }
            }
            catch (Exception ex)
            {
                // Insert failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        /// <summary>
        /// Edit Collection Method
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool ResetPassword(IEnumerable<UserModel> collection,
            out int success, out int failed)
        {
            success = 0;
            failed = 0;
            bool result = false;

            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    User entity;

                    foreach (var item in collection)
                    {
                        // Get single entity from database
                        entity = biocode.Users.Where(a => a.Username == item.Username).SingleOrDefault();

                        if (entity != null)
                        {
                            // Update entity value
                            item.Password = Encrypt.Encode("MD5", "1234");
                            UserMapper.ToEntity(entity, item);

                            item.IsEdit = false;
                        }
                    }

                    try
                    {
                        biocode.SubmitChanges(ConflictMode.ContinueOnConflict);
                    }
                    catch (ChangeConflictException e)
                    {
                        Console.WriteLine("Optimistic concurrency error.");
                        Console.WriteLine(e.Message);
                        Console.ReadLine();
                        foreach (ObjectChangeConflict occ in biocode.ChangeConflicts)
                        {
                            MetaTable metatable = biocode.Mapping.GetTable(occ.Object.GetType());
                            User entityInConflict = (User)occ.Object;
                            Console.WriteLine("Table name: {0}", metatable.TableName);
                            Console.Write("User ID: ");
                            Console.WriteLine(entityInConflict.Username);
                            var model = collection.Where(m => m.Username == entityInConflict.Username).SingleOrDefault();
                            if (model != null)
                            {
                                model.IsEdit = true;
                                foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                                {
                                    object currVal = mcc.CurrentValue;
                                    object origVal = mcc.OriginalValue;
                                    object databaseVal = mcc.DatabaseValue;
                                    MemberInfo mi = mcc.Member;
                                    Console.WriteLine("Member: {0}", mi.Name);
                                    Console.WriteLine("current value: {0}", currVal);
                                    Console.WriteLine("original value: {0}", origVal);
                                    Console.WriteLine("database value: {0}", databaseVal);
                                }
                            }

                            failed++;
                        }
                    }
                    success = collection.Count() - failed;
                    result = (failed == 0);
                }
            }
            catch (Exception ex)
            {
                // Insert failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        /// <summary>
        /// Save Method
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool Save(IList<UserModel> collection,
            out int success, out int failed)
        {
            success = 0;
            failed = 0;
            bool result = false;
            try
            {
                // The collection only include items whose value is isNew, isEdit and isDelete
                if (collection != null && collection.Count > 0)
                {
                    using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                    {
                        foreach (var item in collection)
                        {
                            User entity;
                            if (item.IsNew && !item.IsDelete)
                            {
                                // New entity and set value to it
                                entity = new User();
                                UserMapper.ToEntity(entity, item);

                                // Execute Insertion
                                biocode.Users.InsertOnSubmit(entity);

                                item.IsNew = false;
                                //item.FakeType = UpdateStatus.Insert;
                                //item.MessageFailed = String.Empty;
                            }
                            else if (item.IsDelete)
                            {
                                // Get entity from database
                                entity = biocode.Users.Where(e => e.Username == item.Username).SingleOrDefault();

                                // If entity exists
                                if (entity != null)
                                {
                                    // Execute deletion
                                    biocode.Users.DeleteOnSubmit(entity);

                                    item.IsDelete = false;
                                    //item.FakeType = UpdateStatus.Delete;
                                    //item.MessageFailed = String.Empty;
                                }
                            }
                            else if (item.IsEdit)
                            {
                                // Get single entity from database
                                entity = biocode.Users.Where(a => a.Username == item.Username).SingleOrDefault();

                                if (entity != null)
                                {
                                    // Update entity value
                                    UserMapper.ToEntity(entity, item);

                                    item.IsEdit = false;
                                    //item.FakeType = UpdateStatus.Update;
                                    //item.MessageFailed = String.Empty;
                                }
                            }
                        }

                        try
                        {
                            biocode.SubmitChanges(ConflictMode.ContinueOnConflict);
                        }
                        catch (ChangeConflictException e)
                        {
                            Console.WriteLine("Optimistic concurrency error.");
                            Console.WriteLine(e.Message);
                            Console.ReadLine();
                            foreach (ObjectChangeConflict occ in biocode.ChangeConflicts)
                            {
                                MetaTable metatable = biocode.Mapping.GetTable(occ.Object.GetType());
                                User entityInConflict = (User)occ.Object;
                                Console.WriteLine("Table name: {0}", metatable.TableName);
                                Console.Write("User ID: ");
                                Console.WriteLine(entityInConflict.Username);
                                var model = collection.Where(m => m.Username == entityInConflict.Username).SingleOrDefault();
                                if (model != null)
                                {
                                    //switch (model.FakeType)
                                    //{
                                    //    case UpdateStatus.Insert:
                                    //        model.IsNew = true;
                                    //        break;
                                    //    case UpdateStatus.Update:
                                    //        model.IsEdit = true;
                                    //        break;
                                    //    case UpdateStatus.Delete:
                                    //        model.IsDelete = true;
                                    //        break;
                                    //}
                                    foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                                    {
                                        object currVal = mcc.CurrentValue;
                                        object origVal = mcc.OriginalValue;
                                        object databaseVal = mcc.DatabaseValue;
                                        MemberInfo mi = mcc.Member;
                                        // model.MessageFailed = String.Format("Member: {0}\ncurrent value: {1}\noriginal value: {2}\ndatabase: {3}", mi.Name, currVal, origVal, databaseVal);
                                        Console.WriteLine("Member: {0}", mi.Name);
                                        Console.WriteLine("current value: {0}", currVal);
                                        Console.WriteLine("original value: {0}", origVal);
                                        Console.WriteLine("database value: {0}", databaseVal);
                                    }
                                }

                                failed++;
                            }
                        }

                        // Save successful
                        success = collection.Count - failed;
                        result = (failed == 0);
                    }
                }
            }
            catch (Exception ex)
            {
                // Save failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        /// <summary>
        /// Delete Method
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool Delete(UserModel item)
        {
            bool result = false;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    // Get entity from database
                    User entity = biocode.Users.Where(e => e.Username == item.Username).SingleOrDefault();

                    // Check exist
                    if (entity != null)
                    {
                        // Execute deletion
                        biocode.Users.DeleteOnSubmit(entity);
                        biocode.SubmitChanges();

                        // Delete successful
                        result = true;
                    }
                }
            }
            catch (Exception ex)
            {
                // Delete failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        /// <summary>
        /// Delete Collection Method
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public bool Delete(IList<UserModel> collection,
            out int success, out int failed)
        {
            success = 0;
            failed = 0;
            bool result = false;

            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    User entity;
                    foreach (var item in collection)
                    {
                        // Get entity from database
                        entity = biocode.Users.Where(e => e.Username == item.Username).SingleOrDefault();

                        // If entity exists
                        if (entity != null)
                        {
                            // Execute deletion
                            biocode.Users.DeleteOnSubmit(entity);

                            item.IsDelete = false;
                            //item.MessageFailed = String.Empty;
                        }
                    }

                    try
                    {
                        biocode.SubmitChanges(ConflictMode.ContinueOnConflict);
                    }
                    catch (ChangeConflictException e)
                    {
                        Console.WriteLine("Optimistic concurrency error.");
                        Console.WriteLine(e.Message);
                        Console.ReadLine();
                        foreach (ObjectChangeConflict occ in biocode.ChangeConflicts)
                        {
                            MetaTable metatable = biocode.Mapping.GetTable(occ.Object.GetType());
                            User entityInConflict = (User)occ.Object;
                            Console.WriteLine("Table name: {0}", metatable.TableName);
                            Console.Write("User ID: ");
                            Console.WriteLine(entityInConflict.Username);
                            var model = collection.Where(m => m.Username == entityInConflict.Username).SingleOrDefault();
                            if (model != null)
                            {
                                model.IsDelete = true;
                                foreach (MemberChangeConflict mcc in occ.MemberConflicts)
                                {
                                    object currVal = mcc.CurrentValue;
                                    object origVal = mcc.OriginalValue;
                                    object databaseVal = mcc.DatabaseValue;
                                    MemberInfo mi = mcc.Member;
                                    //model.MessageFailed = String.Format("Member: {0}\ncurrent value: {1}\noriginal value: {2}\ndatabase: {3}", mi.Name, currVal, origVal, databaseVal);
                                    Console.WriteLine("Member: {0}", mi.Name);
                                    Console.WriteLine("current value: {0}", currVal);
                                    Console.WriteLine("original value: {0}", origVal);
                                    Console.WriteLine("database value: {0}", databaseVal);
                                }
                            }

                            failed++;
                        }
                    }
                    success = collection.Count - failed;
                    result = (failed == 0);
                }
            }
            catch (Exception ex)
            {
                // Delete failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        #endregion

        #region Authentication

        public bool Authentication(ref UserModel user)
        {
            bool result = false;
            try
            {
                using (VssTqhDataContext biocode = new VssTqhDataContext(define.CONNECTIONSTRING))
                {
                    string username = user.Username;
                    string password = user.Password;
                    // Get last entity from database
                    var entity = biocode.Users.SingleOrDefault(u => u.Username == username && u.Password == password);

                    // If entity exists
                    if (entity != null)
                    {
                        // Set value to model
                        user = UserMapper.ToModel(entity);
                        result = true;
                    }
                }
            }
            catch (Exception ex)
            {
                // Get last failed
                Debug.WriteLine(ex.Message);
            }

            return result;
        }

        #endregion
    }
}
